createVadbTable <- function(con, tblname, boolcols, decimalcols) {
  sqlcmd <- function() {
    sql <- sprintf("CREATE TABLE IF NOT EXISTS `%s` (",tblname)
    sql <- paste(sql, "`id` int(10) unsigned NOT NULL AUTO_INCREMENT,")
    sql <- paste(sql, "`symbol` varchar(10) COLLATE latin1_general_ci DEFAULT NULL,
                 `date` date NOT NULL,")
    for (boolcol in boolcols) {
      sql <- paste(sql, sprintf("`b_%s` tinyint(1) NOT NULL,",boolcol))
    }
    for (decimalcol in decimalcols) {
      sql <- paste(sql, sprintf("`d_%s` decimal(10,4) DEFAULT NULL,",decimalcol))      
    }
    sql <- paste(sql, "PRIMARY KEY(`id`) ) ENGINE=MyISAM")
    return (sql)
  }
  dbSendQuery(con, sqlcmd())
}

createVadbTable_an <- function(con, tblname, boolcols) {
  decimalcols <- c("1stQ", "Median", "Mean", "3rdQ")
  entries <- c("c0", "o1", "c1")
  sqlcmd <- function() {
    sql <- sprintf("CREATE TABLE IF NOT EXISTS `%s_an` (",tblname)
    sql <- paste(sql, "`id` int(10) unsigned NOT NULL,")
    sql <- paste(sql, "`obs` int(10) unsigned,")    
    for (boolcol in boolcols) {
      sql <- paste(sql, sprintf("`%s` tinyint(1) NOT NULL,",boolcol))
    }
    for (entry in entries) {
      # decimal columns for buy signal
      for (decimalcol in decimalcols) {
        sql <- paste(sql, sprintf("`d_%s_b_%s` decimal(10,4) DEFAULT NULL,", entry, decimalcol))      
      }
      # same for sell signal
      for (decimalcol in decimalcols) {
        sql <- paste(sql, sprintf("`d_%s_s_%s` decimal(10,4) DEFAULT NULL,", entry, decimalcol))      
      }
    }
    sql <- paste(sql, "PRIMARY KEY(`id`) ) ENGINE=MyISAM")
    return (sql)
  }
  dbSendQuery(con, sqlcmd())
}

# create table that contains gap observations
createVadbTable_Gaps <- function(con, tblname="RESEARCH_GAPS") {
  sqlcmd <- function() {
    sql <- sprintf("CREATE TABLE IF NOT EXISTS `%s` (",tblname)
    sql <- paste(sql, "
    `group` varchar(20) NOT NULL,
    `symbol` varchar(10) NOT NULL,
    `date` date NOT NULL,
    `direction` tinyint(4) NOT NULL COMMENT '+1 for gap up, -1 for gap down',
    `CColor` tinyint(4) NOT NULL COMMENT 'candle color of candle after gap',
    `atr` decimal(10,4) NOT NULL COMMENT 'atr_20 value of candle before gap',
    `AvPts` decimal(10,4) NOT NULL COMMENT 'difference btwn OHLC_Average of t and t-1',
    `AARatio` decimal(10,4) NOT NULL COMMENT 'AvPts / atr',
     PRIMARY KEY (`group`,`symbol`,`date`)
     ) ENGINE=MyISAM")
    return (sql)
  }
  dbSendQuery(con, sqlcmd())
}

# create table to research vola clusters
createVadbTable_Vola <- function(con, tblname="RESEARCH_VOLA") {
  sqlcmd <- function() {
    sql <- sprintf("CREATE TABLE IF NOT EXISTS `%s` (",tblname)
    sql <- paste(sql, "
    `symbol` varchar(10) NOT NULL,
    `date` date NOT NULL,
    `ATRs` decimal(10,4),
    `ATRl` decimal(10,4),
    `ATRDiff` decimal(10,4),
    `vThreshold` decimal(10,4) NOT NULL,
    `sig_VgtTh` tinyint(1) COMMENT 'Volatility greater than threshold',
    `sig_VcdTh` tinyint(1) COMMENT 'Current Volatility crosses down threshold',
    `5d_b_s` decimal(10,4),
    `5d_s_s` decimal(10,4),
    `5d_ret` decimal(10,4),
    `5d_volaPct` decimal(10,4) COMMENT 'ATRs_future*100/ATRs_current',
    `10d_b_s` decimal(10,4),
    `10d_s_s` decimal(10,4),
    `10d_ret` decimal(10,4),
    `10d_volaPct` decimal(10,4) COMMENT 'ATRs_future*100/ATRs_current',
    `20d_b_s` decimal(10,4),
    `20d_s_s` decimal(10,4),
    `20d_ret` decimal(10,4),
    `20d_volaPct` decimal(10,4) COMMENT 'ATRs_future*100/ATRs_current',
    `40d_b_s` decimal(10,4),
    `40d_s_s` decimal(10,4),
    `40d_ret` decimal(10,4),
    `40d_volaPct` decimal(10,4) COMMENT 'ATRs_future*100/ATRs_current',
     PRIMARY KEY (`symbol`,`date`)
     ) ENGINE=MyISAM")
    return (sql)
  }
  dbSendQuery(con, sqlcmd())
}